package com.kelai.service.impl;

import com.google.common.collect.Sets;
import com.kelai.common.InfoPage;
import com.kelai.common.PageInfo;
import com.kelai.common.constants.ShopStatusEnum;
import com.kelai.common.constants.WebAppConstants;
import com.kelai.domain.CustomerEntity;
import com.kelai.domain.ShopDataMonthEntity;
import com.kelai.repository.ICustomerRepository;
import com.kelai.service.IShopMonthService;
import com.kelai.utils.BreezeeUtils;
import com.kelai.utils.ContextUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.olap4j.*;
import org.olap4j.metadata.Member;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.DriverManager;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 店铺月统计服务
 * Created by Luffy on 2017/04/21.
 */
@Service("shopMonthService")
public class DefaultShopMonthService implements IShopMonthService {
    private final static Logger logger = LoggerFactory.getLogger(DefaultShopMonthService.class);
    private final static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private final ICustomerRepository customerRepository;

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public DefaultShopMonthService(ICustomerRepository customerRepository, JdbcTemplate jdbcTemplate) {
        this.customerRepository = customerRepository;
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<ShopDataMonthEntity> listAll(Map condition) {

        PageInfo pageInfo = new PageInfo(condition);
        if (condition.get("_sort") != null) {
            pageInfo.setSort((Sort) condition.get("_sort"));
        } else {
            pageInfo.setSort(new Sort(Sort.Direction.ASC, "rowNum","id"));
        }

        List<ShopDataMonthEntity> l = new ArrayList<>();
        try {
            ICustomerRepository customerRepository = ContextUtil.getBean("customerRepository", ICustomerRepository.class);
            logger.info("--ShopMonthService listAll--");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+ WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");

            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append(" {  [组织].[组织ID].[组织ID].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS * ");
            strPageBuffer.append(" [时间].[月].[月].ALLMEMBERS } on 1 ");
            strPageBuffer.append(" FROM (  SELECT ( { [时间].[年月日小时].[月].&["+String.valueOf(condition.get("_startDate")).substring(0,7)+"]:[时间].[年月日小时].[月].&["+String.valueOf(condition.get("_endDate")).substring(0,7)+"]},{ ");

            if(StringUtils.isNotEmpty(condition.get("_customerId")+"") &&
                    (StringUtils.isEmpty(condition.get("customer_id_obj_ae")+"")||condition.get("customer_id_obj_ae")==null)&&(condition.get("_shopIds") == null || CollectionUtils.isEmpty((List<String>)condition.get("_shopIds")))){
                CustomerEntity parentCustomer = customerRepository.findOne(condition.get("_customerId")+"");
                Set<CustomerEntity> shops = Sets.newConcurrentHashSet();
                parentCustomer.findDeepChildren(shops);
                Object shopType = condition.get("shopType");
                shops.stream().forEach( shop -> {
                    if(Objects.equals(shop.getStatus(), ShopStatusEnum.ENABLE.getValue())) {
                        if (shopType != null && !Objects.equals("", shopType)) {
                            if (Objects.equals(shop.getShopType(), shopType)) {
                                strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                            }
                        } else {
                            strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                        }
                    }
                });
                strPageBuffer.append("  [组织].[组织层次].&[-1] ");
            }else if(condition.get("_shopIds") != null && CollectionUtils.isNotEmpty((List<String>)condition.get("_shopIds"))){
                ((List<String>)condition.get("_shopIds")).stream().forEach( shopId -> {
                    strPageBuffer.append("  [组织].[组织层次].&["+shopId+"] , ");
                });
                strPageBuffer.append("  [组织].[组织层次].&[-1] ");
            }else{
                strPageBuffer.append("  [组织].[组织层次].&["+condition.get("customer_id_obj_ae")+"]  ");
            }

            strPageBuffer.append(" } ) ON COLUMNS ");
            strPageBuffer.append(" FROM [客流]) ");

            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + ss[2];
                ShopDataMonthEntity sdde = new ShopDataMonthEntity();
                sdde.setId(id);
                sdde.setCode(id);
                CustomerEntity ce = customerRepository.findOne(ss[0]);
                sdde.setCustomer(ce);
                sdde.setCustomerId(ce.getParentId());
                try{
                    sdde.setHappenTime(BreezeeUtils.DATE_FORMAT_MONTH.parse(ss[1] + "-" + ss[2]));
                }catch(Exception e){
                    logger.info(e.getMessage()+" "+ss[1] + "-" + ss[2]);
                    sdde.setHappenTime(BreezeeUtils.DATE_FORMAT_MONTH.parse("1970-01"));
                }
                sdde.setYear(Integer.parseInt(ss[1]));
                if(ss.length < 4){
                    sdde.setDxin(0);
                    sdde.setDxout(0);
                }else{
                    sdde.setDxin(Integer.parseInt(ss[3]));
                    sdde.setDxout(Integer.parseInt(ss[4]));
                }
                l.add(sdde);
            }

        } catch (Exception e1) {
            e1.printStackTrace();
        }

        return l;
    }

    @Override
    public InfoPage<ShopDataMonthEntity> pageAll(Map condition) {
        PageInfo pageInfo = new PageInfo(condition);
        if (condition.get("_sort") != null) {
            pageInfo.setSort((Sort) condition.get("_sort"));
        } else {
            pageInfo.setSort(new Sort(Sort.Direction.ASC, "rowNum","id"));
        }

        List<ShopDataMonthEntity> l = new ArrayList<>();
        String cnt = "0";
        try {
            ICustomerRepository customerRepository = ContextUtil.getBean("customerRepository", ICustomerRepository.class);
            logger.info("--ShopMonthService pageAll--");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            Integer pageStart = Integer.parseInt(condition.get("pageNumber").toString())*
                    Integer.parseInt(condition.get("pageSize").toString());
            Object shopType = condition.get("shopType");

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");
            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append("  {  subset ");
            strPageBuffer.append(" (( ");
            strPageBuffer.append(" [组织].[组织ID].[组织ID].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS * ");
            strPageBuffer.append(" [时间].[月].[月].ALLMEMBERS ) ");
            strPageBuffer.append(" ,"+pageStart+","+condition.get("pageSize")+")} on 1 ");
            strPageBuffer.append(" FROM (  SELECT ( { [时间].[年月日小时].[月].&["+String.valueOf(condition.get("_startDate")).substring(0,7)+"]:[时间].[年月日小时].[月].&["+String.valueOf(condition.get("_endDate")).substring(0,7)+"]},{ ");

            if(StringUtils.isNotEmpty(condition.get("_customerId")+"") &&
                    (StringUtils.isEmpty(condition.get("customer_id_obj_ae")+"")||condition.get("customer_id_obj_ae")==null)&&(condition.get("_shopIds") == null || CollectionUtils.isEmpty((List<String>)condition.get("_shopIds")))){
                CustomerEntity parentCustomer = customerRepository.findOne(condition.get("_customerId")+"");
                Set<CustomerEntity> shops = Sets.newConcurrentHashSet();
                parentCustomer.findDeepChildren(shops);
                shops.stream().forEach( shop -> {
                    if(Objects.equals(shop.getStatus(), ShopStatusEnum.ENABLE.getValue())) {
                        if (shopType != null && !Objects.equals("", shopType)) {
                            if (Objects.equals(shop.getShopType(), shopType)) {
                                strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                            }
                        } else {
                            strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                        }
                    }
                });
                strPageBuffer.append("  [组织].[组织层次].&[-1] ");
            }else if(condition.get("_shopIds") != null && CollectionUtils.isNotEmpty((List<String>)condition.get("_shopIds"))){
                ((List<String>)condition.get("_shopIds")).stream().forEach( shopId -> {
                    strPageBuffer.append("  [组织].[组织层次].&["+shopId+"] , ");
                });
                strPageBuffer.append("  [组织].[组织层次].&[-1] ");
            }else{
                strPageBuffer.append("  [组织].[组织层次].&["+condition.get("customer_id_obj_ae")+"]  ");
            }

            strPageBuffer.append("  } ) ON COLUMNS ");
            strPageBuffer.append(" FROM [客流]) ");

            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + ss[2];
                ShopDataMonthEntity sdde = new ShopDataMonthEntity();
                sdde.setId(id);
                sdde.setCode(id);
                CustomerEntity ce = customerRepository.findOne(ss[0]);
                sdde.setCustomer(ce);
                sdde.setCustomerId(ce.getParentId());
                try{
                    sdde.setHappenTime(BreezeeUtils.DATE_FORMAT_MONTH.parse(ss[1] + "-" + ss[2]));
                }catch(Exception e){
                    logger.info(e.getMessage()+" "+ss[1] + "-" + ss[2]);
                    sdde.setHappenTime(BreezeeUtils.DATE_FORMAT_MONTH.parse("1970-01"));
                }
                sdde.setYear(Integer.parseInt(ss[1]));
                if(ss.length < 4){
                    sdde.setDxin(0);
                    sdde.setDxout(0);
                }else{
                    sdde.setDxin(Integer.parseInt(ss[3]));
                    sdde.setDxout(Integer.parseInt(ss[4]));
                }
                l.add(sdde);
            }


//        System.out.println(" 客户       门店       设备名称 设备位置 进店数 出店数 时间");
//        l.forEach( shopDayData -> {
//            System.out.println( shopDayData.getCustomerName() + " "+shopDayData.getShopName() + "     --    --      " + shopDayData.getDxin() + " "+shopDayData.getDxout() + " " +shopDayData.getHappenTime());
//        });

        StringBuffer strCntBuffer = new StringBuffer();
        strCntBuffer.append(" ");
        strCntBuffer.append(" WITH ");
        strCntBuffer.append(" SET [xx] as   ({[组织].[组织ID].[组织ID].ALLMEMBERS * [时间].[年].[年].ALLMEMBERS * [时间].[月].[月].ALLMEMBERS * [Measures].[进]}) ");
        strCntBuffer.append(" MEMBER [Measures].[X] AS count([xx]) ");
        strCntBuffer.append(" SELECT Measures.X ON 0 ");
        strCntBuffer.append(" FROM (  SELECT ( { [时间].[年月日小时].[月].&["+String.valueOf(condition.get("_startDate")).substring(0,7)+"]:[时间].[年月日小时].[月].&["+String.valueOf(condition.get("_endDate")).substring(0,7)+"]}, { ");

            if(StringUtils.isNotEmpty(condition.get("_customerId")+"") &&
                    (StringUtils.isEmpty(condition.get("customer_id_obj_ae")+"")||condition.get("customer_id_obj_ae")==null)&&(condition.get("_shopIds") == null || CollectionUtils.isEmpty((List<String>)condition.get("_shopIds")))){
                CustomerEntity parentCustomer = customerRepository.findOne(condition.get("_customerId")+"");
                Set<CustomerEntity> shops = Sets.newConcurrentHashSet();
                parentCustomer.findDeepChildren(shops);
                shops.stream().forEach( shop -> {
                    if(Objects.equals(shop.getStatus(), ShopStatusEnum.ENABLE.getValue())) {
                        if (shopType != null && !Objects.equals("", shopType)) {
                            if (Objects.equals(shop.getShopType(), shopType)) {
                                strCntBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                            }
                        } else {
                            strCntBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                        }
                    }
                });
                strCntBuffer.append("  [组织].[组织层次].&[-1] ");
            }else if(condition.get("_shopIds") != null && CollectionUtils.isNotEmpty((List<String>)condition.get("_shopIds"))){
                ((List<String>)condition.get("_shopIds")).stream().forEach( shopId -> {
                    strCntBuffer.append("  [组织].[组织层次].&["+shopId+"] , ");
                });
                strCntBuffer.append("  [组织].[组织层次].&[-1] ");
            }else{
                strCntBuffer.append("  [组织].[组织层次].&["+condition.get("customer_id_obj_ae")+"]  ");
            }

        strCntBuffer.append("  } ) ON COLUMNS ");
        strCntBuffer.append(" FROM [客流]) ");

        CellSet cellSetCnt = stmt.executeOlapQuery(strCntBuffer.toString());

        for (Position rowPos : cellSetCnt.getAxes().get(0)) {
            Cell cell = cellSetCnt.getCell(rowPos);
            cnt = cell.getFormattedValue();
        }
    } catch (Exception e1) {
        e1.printStackTrace();
    }

        return new InfoPage<ShopDataMonthEntity>(l,new Long(cnt));
    }
}
